package com.kind.codegen;

import com.alibaba.druid.pool.DruidDataSource;
import freemarker.cache.ClassTemplateLoader;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;


public abstract class BeanBuilder {

    private static final Logger log = LoggerFactory.getLogger(BeanBuilder.class);
    private static DruidDataSource dataSource;

    public static final String MYSQL = "MySql";
    public static final String ORACLE = "oracle";

    /**
     * 邮件模板的存放位置
     */
    private static final String TEMPLATE_PATH = "/template/";
    /**
     * 启动模板缓存
     */
    private static final Map<String, Template> TEMPLATE_CACHE = new HashMap<String, Template>();
    /**
     * 模板文件后缀
     */
    private static final String SUFFIX = ".ftl";


    /**
     * 模板引擎配置
     */
    public static Configuration initCfg() {
        Configuration cfg = new Configuration();
        cfg.setTemplateLoader(new ClassTemplateLoader(
                BeanBuilder.class, TEMPLATE_PATH));
        cfg.setEncoding(Locale.getDefault(), "UTF-8");
        cfg.setDateFormat("yyyy-MM-dd HH:mm:ss");
        return cfg;
    }

    public static String removeUnderLineAndcapitalize(String columnName) {
        String field = "";
        String[] fields = columnName.split("_");
        for (int i = 0; i < fields.length; i++) {

            if (i != 0) {
                fields[i] = StringUtils.capitalize(fields[i]);
            }
            field += fields[i];
        }
        return field;
    }

    /**
     * @param sql             sql语句
     * @param src             路径
     * @param clazz
     * @param name
     * @param config
     * @param databaseType    数据类型(目前支持mysql和oracle)
     * @param basePackage(包名)
     * @throws Exception
     */
    public static void buildJavaBean(String sql, String src, String clazz, String name, boolean config, String databaseType, String basePackage) throws Exception {
        // 1.1处理SQL查询结果

        PropertiesManager pm = PropertiesManagerUtil.getPropertiesManager(databaseType);

        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(pm.getString("jdbc.driver"));
        dataSource.setUrl(pm.getString("jdbc.url"));
        dataSource.setUsername(pm.getString("jdbc.username"));
        dataSource.setPassword(pm.getString("jdbc.password"));
        dataSource.setValidationQuery(pm.getString("druid.validationQuery"));
        Connection connection = dataSource.getConnection();
        Statement stmt = connection.createStatement();
        stmt.execute(sql);

        ResultSetMetaData meta = stmt.getResultSet().getMetaData();

        List<TableColumn> columns = getTableColumns(meta);

        stmt.close();
        connection.close();

        // 1.2查询主键
        List<String> pk = null;
        if (databaseType.equals(MYSQL)) {
            pk = getMySQLPK(dataSource, clazz);
        } else if (databaseType.equals(ORACLE)) {
            pk = getOraclePK(dataSource, clazz);
        }
        generateCode(sql, src, clazz, config, databaseType, basePackage, columns, pk);


    }

    private static void generateCode(String sql, String src, String clazz, boolean config, String databaseType, String basePackage, List<TableColumn> columns, List<String> pk) throws IOException, TemplateException {

        Configuration cfg = initCfg();

        Map<String, Object> root = buildProperties(sql, clazz, basePackage, columns, pk);


        /**
         * 处理文件地址
         */
        String modelFilePath = src + File.separatorChar + clazz.replace('.', File.separatorChar);

        String cfgFilePath = src + File.separatorChar + clazz.replace('.', File.separatorChar).replace("model", "mapper") + "Mapper";

        String serviceFilePath = src + File.separatorChar + clazz.replace('.', File.separatorChar).replace("model", "service") + "Service";

        String serviceImplFilePath = src + File.separatorChar + clazz.replace('.', File.separatorChar).replace("model", "service") + "ServiceImpl";

        String dtoFilePath = src + File.separatorChar + clazz.replace('.', File.separatorChar).replace("model", "dto");

        String controllerFilePath = src + File.separatorChar + clazz.replace('.', File.separatorChar).replace("model", "controller");

        /**
         *生成domain
         */
        Template t = cfg.getTemplate("domain.ftl");
        File modelFile = new File(modelFilePath + ".java");
        modelFile.getParentFile().mkdirs();
        t.process(root, new OutputStreamWriter(new FileOutputStream(modelFile)));

        /**
         *生成MyBatis配置和mapper
         */
        if (config) {
            if (databaseType.equals(MYSQL)) {
                t = cfg.getTemplate("mybatis_mysql.ftl");
            } else if (databaseType.equals(ORACLE)) {
                t = cfg.getTemplate("mybatis_oracle.ftl");
            }

            File cfgXMLFile = new File(cfgFilePath + ".xml");
            cfgXMLFile.getParentFile().mkdirs();
            t.process(root, new OutputStreamWriter(new FileOutputStream(cfgXMLFile)));

            t = cfg.getTemplate("mapper.ftl");
            File cfgJavaFile = new File(cfgFilePath + ".java");
            cfgJavaFile.getParentFile().mkdirs();
            t.process(root, new OutputStreamWriter(new FileOutputStream(cfgJavaFile)));
        }

        /**
         *生成service 和serviceImpl
         */
        if (config) {

            t = cfg.getTemplate("service.ftl");
            File serviceFile = new File(serviceFilePath + ".java");
            serviceFile.getParentFile().mkdirs();
            t.process(root, new OutputStreamWriter(new FileOutputStream(serviceFile)));

            t = cfg.getTemplate("serviceImpl.ftl");
            File serviceImplFile = new File(serviceImplFilePath + ".java");
            serviceImplFile.getParentFile().mkdirs();
            t.process(root, new OutputStreamWriter(new FileOutputStream(serviceImplFile)));

            /**
             * 生成分页查询条件
             */
            t = cfg.getTemplate("pageQuery.ftl");
            File searchFile = new File(dtoFilePath + "Query.java");
            searchFile.getParentFile().mkdirs();
            t.process(root, new OutputStreamWriter(new FileOutputStream(searchFile)));

            /**
             *生成 页面的dto
             */
            t = cfg.getTemplate("dto.ftl");
            File dtoFile = new File(dtoFilePath + "Dto.java");
            dtoFile.getParentFile().mkdirs();
            t.process(root, new OutputStreamWriter(new FileOutputStream(dtoFile)));

            /**
             *生成Controller
             */
            t = cfg.getTemplate("controller.ftl");
            File controllerFile = new File(controllerFilePath + "Controller.java");
            controllerFile.getParentFile().mkdirs();
            t.process(root, new OutputStreamWriter(new FileOutputStream(controllerFile)));

        }
    }

    /**
     * 处理相关属性.
     *
     * @param sql
     * @param clazz
     * @param basePackage
     * @param columns
     * @param pk
     * @return
     */
    private static Map<String, Object> buildProperties(String sql, String clazz, String basePackage, List<TableColumn> columns, List<String> pk) {
        Map<String, Object> root = new HashMap<String, Object>();
        root.put("bean", clazz);
        root.put("search_pkg", clazz.substring(0, clazz.lastIndexOf(".")).replace("model", "dto"));
        root.put("bean_pkg", clazz.substring(0, clazz.lastIndexOf(".")));
        root.put("dto_pkg", clazz.substring(0, clazz.lastIndexOf(".")).replace("model", "dto"));
        root.put("mapper_pkg", clazz.substring(0, clazz.lastIndexOf(".")).replace("model", "mapper"));
        root.put("service_pkg", clazz.substring(0, clazz.lastIndexOf(".")).replace("model", "service"));
        root.put("controller_pkg", clazz.substring(0, clazz.lastIndexOf(".")).replace("model", "controller"));
        root.put("bean_type", clazz.substring(clazz.lastIndexOf(".") + 1));
        root.put("page_query", clazz.substring(clazz.lastIndexOf(".") + 1) + "Query");
        root.put("dto_type", clazz.substring(clazz.lastIndexOf(".") + 1) + "Dto");
        root.put("bean_created", new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
        root.put("bean_author", System.getProperty("user.name"));
        root.put("bean_sql", sql);
        root.put("bean_property", columns);
        //root.put( "bean_parent", parent == null ? null : parent.getName() );
        root.put("bean_pk", pk);
        root.put("mapper_parent", "CommonMapper");
        root.put("service_parent", "BaseService");
        root.put("common_pkg", basePackage);

        root.put("table_name", sql.substring(sql.toLowerCase().lastIndexOf("from") + 4).trim().toUpperCase());
        return root;
    }

    private static List<TableColumn> getTableColumns(ResultSetMetaData meta) throws SQLException {
        List<TableColumn> columns = new ArrayList<TableColumn>();

        for (int i = 1; i <= meta.getColumnCount(); i++) {
            TableColumn o = new TableColumn();
            o.setColumnFields(meta.getColumnName(i));
            o.setFields(removeUnderLineAndcapitalize(meta.getColumnName(i).toLowerCase()));
            // o.setFields( meta.getColumnName( i ) );
            //o.setType(meta.getColumnTypeName(i));
            o.setType(meta.getColumnTypeName(i));

            o.setLength(meta.getPrecision(i));
            o.setScale(meta.getScale(i));
            log.debug("column:" + o);
            log.debug(meta.getColumnName(i) + "," + meta.getColumnTypeName(i)
                    + "," + meta.getScale(i) + "," + meta.getPrecision(i));
            columns.add(o);
        }
        return columns;
    }

    /**
     * 查询主键
     */
    public static List<String> getMySQLPK(DruidDataSource dataSource, String clazz) {
        List<String> pk = new ArrayList<String>();
        try {
            String queryPk = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE " +
                    "WHERE CONSTRAINT_NAME='PRIMARY' and TABLE_SCHEMA=? and  TABLE_NAME=?";

            Connection connection = dataSource.getConnection();
            PreparedStatement prestmt = connection.prepareStatement(queryPk);
            String jdbcUrl = dataSource.getUrl();
            prestmt.setString(1, jdbcUrl.substring(jdbcUrl.lastIndexOf("/") + 1, jdbcUrl.indexOf("?")));
            prestmt.setString(2, clazz.substring(clazz.lastIndexOf(".") + 1).toUpperCase());
            ResultSet rs = prestmt.executeQuery();

            while (rs.next()) {
                log.debug("======mysql==================:" + rs.getString(1));
                pk.add(rs.getString(1));
            }

            rs.close();
            prestmt.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pk;
    }

    //查询主键
    public static List<String> getOraclePK(DruidDataSource dataSource, String clazz) {
        List<String> pk = new ArrayList<String>();
        try {
            String queryPk = "select column_name "
                    + "from (select table_name, constraint_name,owner "
                    + "from user_constraints where constraint_type = 'P' "
                    + "and owner = ? and table_name = ?) uc, user_cons_columns ucc "
                    + "	where uc.constraint_name = ucc.constraint_name"
                    + " and uc.table_name = ucc.table_name"
                    + "	  and uc.owner = ucc.owner" + " and ucc.owner =? "
                    + " and ucc.table_name = ?";

            Connection connection = dataSource.getConnection();

            PreparedStatement prestmt = connection.prepareStatement(queryPk);
            prestmt.setString(1, dataSource.getUsername().toUpperCase());
            prestmt.setString(2, clazz.substring(clazz.lastIndexOf(".") + 1)
                    .toUpperCase());
            prestmt.setString(3, dataSource.getUsername().toUpperCase());
            prestmt.setString(4, clazz.substring(clazz.lastIndexOf(".") + 1)
                    .toUpperCase());

            ResultSet rs = prestmt.executeQuery();

            while (rs.next()) {
                log.debug("=======oracle=================:" + rs.getString(1));
                pk.add(rs.getString(1));
            }

            rs.close();
            prestmt.close();
            connection.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pk;
    }

    public static List<String> printTables(String databaseType) throws Exception {

        List<String> tables = new ArrayList<String>();

        // 1.1处理SQL查询结果

        PropertiesManager pm = PropertiesManagerUtil.getPropertiesManager(databaseType);

        dataSource = new DruidDataSource();
        dataSource.setDriverClassName(pm.getString("jdbc.driver"));
        dataSource.setUrl(pm.getString("jdbc.url"));
        dataSource.setUsername(pm.getString("jdbc.username"));
        dataSource.setPassword(pm.getString("jdbc.password"));
        dataSource.setValidationQuery(pm.getString("druid.validationQuery"));
        Connection connection = dataSource.getConnection();


        DatabaseMetaData md = connection.getMetaData();
        ResultSet rs = md.getTables(null, null, "%", null);
        while (rs.next()) {
            tables.add(rs.getString(3));
        }

        // 将数据库中的表的名称转储出来
//		 String[] types = new String[1]; 
//		 types[0] = "TABLES"; // 设置查询类型
//		 ResultSet results = dmd.getTables(null, null, "%", types);
//		boolean more = results.next(); 
//		 while (more) 
//		 { 
//		// for (i = 1; i <= numCols; i++) 
//		// System.out.print(results.getString(i)+" "); 
//		 System.out.println(results.getString("TABLE_NAME")); 
//		 more = results.next(); 
//		 }
        return tables;
    }


}
